from typing import List, Optional
from fastapi import APIRouter, Depends, Query
from sqlalchemy.orm import Session
from app.db.database import get_db
from app.services.sponsors_service import SponsorsService
from app.repositories.sponsors_record_repo import SponsorsRecordRepo
from app.repositories.sponsor_report_repo import SponsorsReportRepo
from app.schema.sponsors_schema import SponsorRecordItem
from app.schema.pagination import Page, PageMeta

from app.repositories.sponsor_contact_repo import SponsorContactRepo
from app.schema.sponsor_contact_schema import SponsorContactItem
from app.services.sponsor_contact_service import SponsorContactService
from app.utilities.exceptions import AppException
from openpyxl.styles import Alignment
from fastapi.responses import StreamingResponse
import pandas as pd
import io
from fastapi import HTTPException
from collections import defaultdict
import matplotlib
matplotlib.use('Agg') 

import matplotlib.pyplot as plt
from openpyxl import Workbook
from openpyxl.drawing.image import Image as XLImage
import tempfile
import os
from app.utilities.report_helpers import get_scholarship_summary, generate_sponsorship_grid
from openpyxl.utils import get_column_letter
import matplotlib.patches as patches


router = APIRouter(prefix="/sponsors", tags=["sponsors"])

@router.get("/records", response_model=Page[SponsorRecordItem], summary="get sponsor records")
def sponsor_records(
    db: Session = Depends(get_db),
    year_from: int = Query(..., description="Program Year from"),
    year_to: int = Query(..., description="Program Year to"),
    current: bool = Query(None, description="only Current"),
    program_id: Optional[int] = Query(None),
    program_code: Optional[List[str]] = Query(None, description="Multiple program codes like ACT, BIS"),
    keyword: Optional[str] = Query(None),
    faculty: Optional[List[str]] = Query(None, description="Multiple faculties like Business, Engineering"),
    coop_program: Optional[List[str]] = Query(None, description="Multiple co-op programs like Business, Technology"),
    page: int = Query(1, ge=1),
    page_size: int = Query(10, ge=1, le=100),
):
    svc = SponsorsService(SponsorsRecordRepo(db))
    
    total, items = svc.list_records(
        year_from, year_to, current,
        program_id, program_code, keyword, faculty, coop_program,
        page, page_size
    )
    return Page[SponsorRecordItem](
        meta=PageMeta(total=total, page=page, page_size=page_size),
        items=items
    )

    
@router.get("/{sponsor_id}/contacts", response_model=List[SponsorContactItem], summary="Query contacts by Sponsor ID, optional kc/inv filter")
def list_sponsor_contact_by_sponsor_id(
    sponsor_id: int,
    kc: Optional[bool] = Query(None),
    inv: Optional[bool] = Query(None),
    db: Session = Depends(get_db),
):
    svc = SponsorContactService(SponsorContactRepo(db))
    return svc.list_contacts_by_sponsor_id(sponsor_id, kc, inv)


@router.get("/export-commitment-report", summary="Export Sponsor Commitment Report")
def export_commitment_report(
    query: str = Query(..., description="COCODE or Umbrella Code"),
    db: Session = Depends(get_db)
):
    repo = SponsorsReportRepo(db)
    cocodes, is_umbrella = repo.get_cocodes_from_query(query)
    if not cocodes:
        raise HTTPException(status_code=404, detail=f"No cocode found for query '{query}'")

    data = repo.get_commitment_report_records(cocodes=cocodes)
    if not data:
        raise HTTPException(status_code=404, detail=f"No records found for query '{query}'")

    for row in data:
        row["SPONSORSHIP YEAR"] = int(row["PROGRAM ID"][-2:]) + 2000 if row["PROGRAM ID"] else None

    grouped = defaultdict(lambda: defaultdict(list))
    for row in data:
        grouped[row["COCODE"]][row["SPONSORSHIP YEAR"]].append(row)

    output = io.BytesIO()
    wb = Workbook()
    ws = wb.active
    ws.title = "SponsorCommitment"

    headers = ["COCODE"] * is_umbrella + ["SPONSORSHIP YEAR", "SCHOLARSHIPS", "SCHOLARS", "VISUAL"]
    ws.append(headers)

    row_cursor = 2
    temp_files = []

    for cocode, year_dict in grouped.items():
        for year in sorted(year_dict.keys(), reverse=True):
            students_all = year_dict[year]

            students_sorted = sorted(
                [s for s in students_all if int(s["PROGRAM ID"][-2:]) + 2000 == year],
                key=lambda s: (-s["ITE NUMBER"] or 0)
            )

            summary_text = get_scholarship_summary(students_sorted, year)

            col = 1
            if is_umbrella:
                ws.cell(row=row_cursor, column=col, value=cocode)
                col += 1

            ws.cell(row=row_cursor, column=col, value=year)

            cell_summary = ws.cell(row=row_cursor, column=col + 1, value=summary_text)
            cell_summary.alignment = Alignment(wrap_text=True, vertical="top")

            for idx, s in enumerate(reversed(students_sorted)):
                ws.cell(row=row_cursor + idx, column=col + 2, value=f"{s['FIRST NAME']} {s['LAST NAME']}")

            fig = generate_sponsorship_grid(students_sorted, year)
            tmpfile = tempfile.NamedTemporaryFile(delete=False, suffix=".png")
            tmpfile.close()
            fig.savefig(tmpfile.name, bbox_inches="tight", dpi=150)
            plt.close(fig)

            img = XLImage(tmpfile.name)
            img_col_letter = get_column_letter(col + 3)
            img.anchor = f"{img_col_letter}{row_cursor}"
            ws.add_image(img)

            temp_files.append(tmpfile.name)

            row_cursor += max(20, int(len(students_sorted) * 1.2))

    wb.save(output)
    output.seek(0)
    for f in temp_files:
        os.unlink(f)

    return StreamingResponse(
        output,
        media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={'Content-Disposition': f'attachment; filename="sponsor_commitment_report_{query}.xlsx"'}
    )